﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BIW;
using yunbo.Model.SSM;

namespace yunbo.Repositorys.BIWRepositorys
{
    public class Wbd_ckRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public FYwbd_ck GetFYwbd_Ck(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"SELECT * FROM [dbo].[wbd_ck]
                                WHERE [is_deleted] = 1
                                ORDER BY [ck_id]
                                OFFSET ({PageNumber}-1)*{PageSize} ROWS
                                FETCH NEXT {PageSize} ROWS ONLY;";
                List<Wbd_ck> wbd_Cks=connection.Query<Wbd_ck>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[wbd_ck]";
                int zon=connection.Query<int>(sql1).FirstOrDefault();
                FYwbd_ck fYwbd_Ck=new FYwbd_ck() 
                { 
                    wbd_Cks = wbd_Cks,
                    zon = zon 
                };
                return fYwbd_Ck;
            }
        }
        public List<Wbd_ck> Wbd_Ck()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "select [ck_code],[ck_name] from [dbo].[wbd_ck] where is_deleted=1";
                List<Wbd_ck> wbd_Cks = connection.Query<Wbd_ck>(sql).ToList();
                return wbd_Cks;
            }
        }
        public int AddWbd_Ck(Wbd_ck wbd_Ck)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"insert into [dbo].[wbd_ck](ck_code, ck_name, ck_kx, cl_dz,is_deleted, ck_zt,ck_mj,ck_fzr, bz, create_by, create_time )
                               values ('{wbd_Ck.ck_code}','{wbd_Ck.ck_name}','{wbd_Ck.ck_kx}','{wbd_Ck.cl_dz}','{wbd_Ck.is_deleted}','{wbd_Ck.ck_zt}','{wbd_Ck.ck_mj}','{wbd_Ck.ck_fzr}','{wbd_Ck.bz}','{wbd_Ck.create_by}','{wbd_Ck.create_time}');";
                int ck = connection.Execute(sql);
                return ck;
            }
        }
        public int UpdateWbd_Ck(Wbd_ck wbd_Ck)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wbd_ck] set ck_code='{wbd_Ck.ck_code}', ck_name='{wbd_Ck.ck_name}', ck_kx='{wbd_Ck.ck_kx}', cl_dz='{wbd_Ck.cl_dz}',ck_mj='{wbd_Ck.ck_mj}',ck_fzr='{wbd_Ck.ck_fzr}', bz='{wbd_Ck.bz}', update_by='{wbd_Ck.update_by}', update_time='{wbd_Ck.update_time}' where ck_id='{wbd_Ck.ck_id}';";
                int ck = connection.Execute(sql);
                return ck;
            }
        }

        public int UpIsDeleted(int id) 
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wbd_ck] set is_deleted=0 where ck_id='{id}'";
                int ck= connection.Execute(sql);
                return ck;
            }
        }
        public int UpZt(int id,int zt) 
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wbd_ck] set [ck_zt]='{zt}'\r\nwhere [ck_id]='{id}'";
                int ckt = connection.Execute(sql);
                return ckt;
            }
        }
        public FYwbd_ck MHCX(int PageNumber, int PageSize, int? zt, string? name)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT * FROM [dbo].[wbd_ck] WHERE ck_zt like '%{zt}%' and ck_name like '%{name}%'\r\nORDER BY  [ck_id] \r\nOFFSET ({PageNumber}-1)*{PageSize} ROWS\r\nFETCH NEXT {PageSize} ROWS ONLY;";
                List<Wbd_ck> cks = connection.Query<Wbd_ck>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[wbd_ck] WHERE ck_zt like '%{zt}%' and ck_name like '%{name}%'";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYwbd_ck ck = new FYwbd_ck()
                { 
                    wbd_Cks = cks,
                    zon = zon,
                };
                return ck;
            }
        }
        public Wbd_ck GetCkbycode(string code) 
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select ck_name from [dbo].[wbd_ck] where [ck_code]='{code}'";
                Wbd_ck ck = connection.Query<Wbd_ck>(sql).FirstOrDefault();
                return ck;
            }
        }
    }
}
